SQL Summary

Creating Tables

create table unit
(
    unit_code  char(7)  not null,
    unit_name  varchar2(50) constraint uq_unit_name unique not null,
    student_id number(7, 0)  not null,
    constraint pk_unit primary key (unit_code)
    constraint fk_student foreign key (student_id) references student(student_id)
);

And another way to add constraint:

1 2	alter table unit add (constraint fk_student foreign key (student_id) references student(student_id));

The difference between varchar and varchar2: VARCHAR is reserved by Oracle to support distinction between NULL and empty string in future, as ANSI standard prescribes. VARCHAR2 does not distinguish between a NULL and empty string, and never will.

Referential Integrity

RESTRICT :
Deletion of tuples is NOT ALLOWED for those tuples in the table referred by the FK (the table containing PK) if there is corresponding tuple in the table containing the FK.
CASCADE : A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the deletion of the corresponding tuples in the table containing the FK.
NULLIFY : A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the update of the corresponding tuples in the table containing the FK to NULL.

Using Sequence

Oracle supports auto-increment of a numeric PRIMARY KEY.

create sequence sno_seq start with 0 increment by 1;
insert into student values (sno_seq.nextval, 'Bond', 'James', 
                            to_date('01-Jan-1994’, 'DD-MM-YYYY'));
insert into enrolment values (sno_seq.currval, ’FIT9132’);

Alter Tables

Adding columns to the original table :

alter table student
add (stu_address  varchar(200),
     status  char(1)  default 'C',
     constraint status_ck check (status in ('G', 'C'))
     );

Adding referential integrity :

1
2
3

alter table enrolment
add (constraint fk_enrolment_student foreign key (stu_nbr) references student (stu_nbr) on delete cascade, 
     constraint fk_enrolment_unit foreign key (unit_code) references unit (unit_code) on delete cascade);

Add Rows

Use to_char() and to_date() to convert the format between string and date. rollback function can undo the changes.

1	insert into student values (112233, 'Wild', 'Wilbur', to_date('01-Jan-1995 18:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Select Statement

<> means not equal to, this expression can be transplanted to other database platform, != doesn't work in some database version.

1
2
3

select stu_id, stu_fname, stu_lname
from student
where stu_fname = 'Dylan';

Range

1
2
3

select staff_info 
from staff
where salary between 1000 and 3000;

Which equals to :

1
2
3

select staff_info
from staff
where salary >= 1000 and salary <= 3000;

Set Membership

To test whether the value of expression equals one of a set of values.

1
2
3

select city_info
from city
where city in ('Melbourne', 'Sydney');

Pattern Match

To test whether a string (text) matches a specified pattern.

% character represents any sequence of zero or more character.
_ character represents any single character.

select city_info
from city
where city like 'M%';

select unit_info 
fron unit
where unit_code like 'FIT50__' 
``` 

### NVL
This function is used to replace a null with a value : 
```SQL
select stu_id,
       nvl(enrol_mark, 0),
       nvl(enrol_grade, 'WH')
from enrolment;

Rename Column

Use QL Summary

Creating Tables

create table unit
(
    unit_code  char(7)  not null,
    unit_name  varchar2(50) constraint uq_unit_name unique not null,
    student_id number(7, 0)  not null,
    constraint pk_unit primary key (unit_code)
    constraint fk_student foreign key (student_id) references student(student_id)
);

And another word 'as' to rename the column in the selection result :

select stu_id, 
       enrol_mark/10 as new_mark
from enrolment;

select stu_id, 
       enrol_mark/10 as "new mark"
from enrolment;

Sort Query Result

Order can be ascending or descending, the default is ascending. Null values can be explicitly placed first/last using nulls last or nulls first command.

1
2
3

select stu_id, enrol_mark
from enrolment
order by enrol_mark desc;

Distinct

The key word "distinct" can be used as part of select clause to remove duplicate rows in the query result :

1
2
3

select distinct stu_id
from enrolment
where enril_mark is null;

Join

select s.stu_id, s.stu_lname, u.unit_name
from unit u 
     join enrolment e on u.unit_code = e.unit_code
     join student s on e.stu_id = s.stu_id
order by s.stu_nbr, u.unit_name;

Add constraint:

1 2	alter table unit add (constraint fk_student foreign key (student_id) references student(student_id));

Referential Integrity

RESTRICT :
Deletion of tuples is NOT ALLOWED for those tuples in the Statement

update enrolment
set mark=85
where unit_code = (select unit_code
                   from unit
                   where unit_name = 'Database')
      and mark = 80;

Delete Statement

delete from enrolment
where sno = '29394678' and
      unit_code = (select unit_code
                   from unit
                   where unit_name = 'Database') and
      semester = '1' and
      year = '2019';

Transactions

Atomicity : all database operations (sql requests) of a transaction must be entirely completed or aborted
Consistency : it must take the datebase from one consistent state to another
Isolation : it must not interfere with oble referred by the FK (the table containing PK) if there is corresponding tuple in the table containing the FK.

CASCADE : A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the deletion of ther concurrent transactions; data used during execution of a transaction cannot be used by a second transaction until the first one is completed

Durability : once completed the changes the transaction made to the data are durable, even in the event of system failure

Lock Types

Shared Lock : multiple processes can simultaneously hold shard locks, to enable them to read without updating : if a transaction \(T_i\) has obtained a shared lock on data item \(Q\), then \(T_i\) can read this item but not write to this item
Exclusive Lock : a process that needs to update a record must obtain an exclusive lock. Its application for a lock will not proceed until all current locks are released : if a transaction \(T_i\) has obtained an exclusive lock on data item \(Q\), then \(T_i\) can both read and write to item \(Q\)

Deadlock

A sample scenario : Transaction 1 has an exclusive lock on data item A, and requests a lock on data item B; Transaction 2 has an exclusive lock on data item B, and requests a lock on data item A. Then deadlock happen.

Deadlock prevention :

A transaction must acquire all the locks it requires before it updates any record
If it cannot acquire a necessory lock, it releases all locks, and tries again later

Check Point

Any transaction that was running at the time of failure needs to be undone and restarted
Any transactions that committed since the last checkpoint need to be redone

Group By

If a group by clause is used with aggregate function, the database will apply the aggregate function to the different groups defined in the clause rather than all rows.

selecrresponding tuples in the table containing the FK.

3. NULLIFY :
    A deletion of a tuple in the table referred by the FK (the table containing PK) will result in the update of the corresponding tuples in the table containing the FK to NULL.

### Using Sequence
Oracle supports auto-increment of a numeric PRIMARY KEY.
```SQL
create sequence sno_seq start with 0 increment by 1;
insert into student values (sno_seq.nextval, 'Bond', 'James', 
                            to_date('01-Jan-1994’, 'DD-MM-YYYY'));
insert into enrolment values (sno_seq.currval, ’FIT9132’);

Alter Tables

Adding columns to the original table :

alter table student
add (stu_address  varchar(200,
     status  char(1)  default 'C',
     constraint status_ck check (status in ('G', 'C'))
     );

Adding referential integrity :

alter table enrolment
add (constraint fk_enrolment_student foreign key (stu_nbr) references student (stu_nbr) on delete cascade, 
     constraint fk_enrolment_unit foreign key (unit_code) references unit (unit_code, avg(mark)
from enrolment
group by unit_co) on delete cascade);

Having Clause

It is u Add Rows **Used to put a condition or conditions on the groups defined by group by clause :

select unit_code, avg(mark), count(*)
from enrolment
group bt unit_code
having avg(mark) > 50;

The where clause is applied to all rows in the table
The having clause is applied to the groups defined by the group by caluse
The order of operations performed is from, where, group by, having and then order by _char() and to_date() to convert the format between string and date**. rollback function can undo the changes.
1
insert into student values (112233, 'Wild', 'Wilbur', to_date('01-Jan-1995 18:00:00', 'DD-MM-YYYY HH24:MI:SS'))

Subqueries

Simple Example

Find all students whose mark is higher than the average mark of all enrolled students :

1
2
3

select *
from enrolment
where mark > (select avg(mark) from enrolment);

Nested

For each unit, find the students who obtained the maximum mark in the unit :

select studid, unitcode, mark
from enrolment
where (unitcode, mark) in (select unitcode, max(mark)
               from enrolment
               group by unitcode);

This subquery is independent of the ourter query and is executed only once.

Correlated

For each unit, find the students who obtained the maximum mark in the unit :

select studid, unitcode, mark
from enrolment e1
where mark = (select max(mark)
        from enrolment e2
        where e1.unitcode = e2.unitcode);

This subquery is related to the outer query and is considered to be evaluated once for each row of the outer query.

Inline (Derived Table)

For each unit, find the students who obtained the maximum mark in the unit :

select studid, e.unitcode, mark as max_mark
from 
  (select unitcode, max(mark)
   from enrolment group by unitcode) max_table
   join enrolment e on e.unitcode = max_table.unitcode and
   e.mark = max_table.max_mark;

Triggers

Row Level Trigger

FOR EACH ROW option : the for each row option determines whether the trigger is a row trigger or a statement trigger. If we specific for each row, the trigger fires once for each row of the table that is affected by the triggering statement. The absence of the for each row option means that the trigger fires only once for each applicable statement, but not separately for each row affected by the statement.

Update the item_code when it is modified in the table 'item' :

create or replace trigger item_update
after update of item_code on item
for each row
begin 
  update item_treatment
    set item_code = :new.item_code
    where item_code = :old.item_code
  dbms_output.put_line('Update Successfully')
end;

Check the validation of last name and first name :

create or replace trigger check_name
before insert or update on patient
for each row
begin
  if :new.patient_fname is null and :new.patient_lname is null then
    raise_application_error(-20000, 'Empty Name Error');
  end if;
end;

If we want to modify the original table, we need to change the :new value not directly writing to the table, via say an update which would cause a mutating table error :

create or replace trigger calculate_grade
before insert or update of enrol_mark on enrolment
for each row
declare
  final_grade enrolment.enrol_grade%type;
begin
  if :new.enrol_mark >= 70 and :new.enrol_mark < 80 then
    final_grade := 'D';
  elsif
    :new.enrol_mark >= 60 and :new.enrol_mark < 70 then
      final_grade := 'C';
  end if;
  :new_enrol_grade := final_grade;
end;

Statement Level Trigger

Executed once for the whole table but will have to check all rows in the table
In many cases, it will be inefficient
No access to the correlation values :new and :old

Views

A virtual table derived from one or more base tables
Sometimes used as access control to the database
Aim : Reduce complexity and enhance security
There's no any true data in the view, these data are dynamically generated when the view is referenced

For each unit, find the students who obtained the maximum mark in the unit :

create or replace view max_view as 
  select unitcode, max(mark) as max_mark
  from enrolment group by unitcode;

select e.studid, e.unitcode, e.mark
from max_view v join enrolment e on e.unitcode = v.unitcode;

Join

Self Join

1
2
3

select *
from employee e1 join employee e2
on e1.mgrno = e2.empno;

Full Join

1 2	select * from student s full outer join mark m on s.id = m.id;

Left Join

1
2
3

select * from
student s left outer join mark m
on s.id = m.id;

Right Join

1
2
3

select * from
student s right outer join mark m
on s.id = m.id;

Relationship

Weak

A weak relationship, also known as a non-identifying relationship, exists if the primary key of the related entity does not contain a primary key component of the parent entity.

Strong

A strong (identifying) relationship exists when the primary key of the related entity contains a primary key component of the entity.

Normalisation

Unormalisation Form

The UNF representation of a relation is the representation which you have mapped from your inspection of the form, and no primary key etc have as yes beeen identified.

Fist Normal Form

A unique primary key has been identified for each tuple/row
It is a valid relation : entity integrity(no part of PK is null); single value for each cell (no repeating group)
All attributes are functionally dependent on all or part of the primary key

UNF to 1NF :

Elminate the repeating groups
Identify the primary key
Identify all dependencies

1NF to 2NF :

Make new tables to eliminate partial dependencies

2NF to 3NF :

Make new tables to eliminate transitive dependencies